package ui;

import javafx.collections.FXCollections;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.*;
import util.CapsJdbc;
import util.CapsRes;
import util.CapsTableView;

import java.net.URL;
import java.util.*;

public class StuMgr implements Initializable {

   @FXML
   private TextField sno;

   @FXML
   private TextField name;

   @FXML
   private ChoiceBox<String> xb;

   @FXML
   private TextField nameCond;

   @FXML
   private TableView<Map<String,String>> stuTv;

   @FXML
   private Pagination pager;
   @FXML
   private ComboBox<Integer> pageNumCB;

   @FXML
   private void saveStu(ActionEvent event) {
      List<Object> params = new ArrayList<>(Arrays.asList(name.getText(),xb.getValue(),sno.getText()));
      String updSql = "update stu set name=?,xb=? where sno=?";
      CapsRes capsRes = CapsJdbc.execSQL(updSql,params);
      if (capsRes.getTtlNum() < 1) {
         String addSql = "insert into stu(name,xb,sno) values(?,?,?)";
         CapsJdbc.execSQL(addSql,params);
      }
      this.dealQry(null, 1);
   }

   @FXML
   private void qryByName(ActionEvent event) {
      this.dealQry(nameCond.getText(), 1);
   }

   private static Integer PAGESIZE = 3;

   @Override
   public void initialize(URL url, ResourceBundle rb) {
      xb.setItems(FXCollections.observableArrayList("男","女"));
      xb.setValue("男");
      pageNumCB.setItems(FXCollections.observableArrayList(3,5,10));
      pageNumCB.setValue(PAGESIZE);
      pageNumCB.getSelectionModel().selectedItemProperty()
              .addListener((observable,oldVal,newVal) -> {
                 int pageNo = this.pager.getCurrentPageIndex()+1;
                 this.dealQry(this.nameCond.getText(),pageNo);
              });
      this.pager.setPageFactory(value -> {
         System.out.println(value);
         this.dealQry(this.nameCond.getText(),value+1);
         return null;
      });
      this.dealQry(null, 1);
   }

   private void dealQry(String condName, Integer currPageNo) {
      String selSql = "select sno,s.name,xb,c.name clsName ";
      String strSQL =  "from stu s left outer join cls c on s.cno=c.cno";
      List<Object> params = new ArrayList<>(5);;
      if (null != condName && condName.length() > 0) {
         strSQL += " where s.name like ?";
         params.add(condName+"%");
      }
      //获取总的记录数
      String ttlSql = "select count(*) as ttlCnt " + strSQL;
      CapsRes capsRes = CapsJdbc.execSQL(ttlSql,params);
      String ttlRec = capsRes.getRs().get(0).get(0);
      Double tmpPages = Math.ceil(Double.parseDouble(ttlRec) / pageNumCB.getValue());
      this.pager.setPageCount(tmpPages.intValue());
      strSQL = selSql + strSQL;
      Integer recNum = pageNumCB.getValue() * (currPageNo - 1);
      strSQL += " limit ?,?";
      params.add(recNum);
      params.add(pageNumCB.getValue());
      capsRes = CapsJdbc.execSQL(strSQL,params);
      CapsTableView.dealTableView(capsRes,stuTv);
      CapsTableView.addButtonToTable(stuTv,(recMap,idCol) -> {
         String delSql = "delete from stu where sno = ?";
         List<Object> delParams = new ArrayList<>(1);
         delParams.add(recMap.get("sno"));
         CapsJdbc.execSQL(delSql,delParams);
         this.dealQry(null, 1);
      },(recMap,idCol) -> {
//          System.out.println(recMap);
           sno.setText(recMap.get("sno"));
           name.setText(recMap.get("name"));
           xb.setValue(recMap.get("xb"));
      });
   }
}
